-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
/*
exec Proc_TA_RptEmployeeForm25 @EmployeeId=N'669,',@EmployeeName=N'',@LevelID=N'3,34',@employeeCategory=N'0',@employeeType=N'0',@UserGroupName=N'SuperAdmin',@GradeLevel=N'0',
@EmpSwipeCard=N'',@ParentId=3,@Fromdate1=N'2024/02/01',@Todate1=N'2024/02/24',@DesignationID=0,@Gender=N'Both',@EmpAutoID=1

*/


-- =============================================
Create PROCEDURE Proc_TA_RptEmployeeForm25
	-- Add the parameters for the stored procedure here
	@EmployeeId NVARCHAR(50)=N'669',
	@EmployeeName NVARCHAR(50)=N'',  
	@LevelID NVARCHAR(1000)=N'3,34', 	 
	@employeeCategory NVARCHAR(50)=N'0',
	@employeeType NVARCHAR(50)=N'0'	,
	@UserGroupName NVARCHAR(250)=N'SuperAdmin',
	@GradeLevel NVARCHAR(20)=N'0',
	@EmpSwipeCard NVARCHAR(20)=N'',
	@ParentId INT=3,
	@Fromdate1 NVARCHAR(50)=N'2024/01/01',
    @Todate1 NVARCHAR(50)=N'2024/01/31',
	@DesignationID INT=0,
	@Gender NVARCHAR(10)=N'Both',
	@EmpAutoID INT=1
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	--Creating Variables AND Temporary Tables for calculation
	DECLARE @loca_EmployeeId NVARCHAR(50)
	DECLARE @loca_EmployeeName NVARCHAR(50)
	DECLARE @loca_LevelID NVARCHAR(1000)
	DECLARE @loca_employeeCategory NVARCHAR(50)
	DECLARE @loca_employeeType NVARCHAR(50)
	DECLARE @loca_UserGroupName NVARCHAR(250)
	DECLARE @loca_GradeLevel NVARCHAR(20)
	DECLARE @loca_EmpSwipeCard NVARCHAR(20)
	DECLARE @loca_ParentId INT
	DECLARE @loca_Fromdate1 NVARCHAR(50)
    DECLARE @loca_Todate1 NVARCHAR(50)
	DECLARE @loca_DesignationID INT
	DECLARE @loca_Gender NVARCHAR(10)
	DECLARE @loca_EmpAutoID INT
	DECLARE @Fromdate2 DATETIME
	DECLARE @Count INT
	DECLARE @Diff INT
	DECLARE @isReportingto INT	

	Declare @TblEmployeeID As Table(EmployeeID NVARCHAR(25))
	Declare @TblEmployeeName As Table(EmployeeID NVARCHAR(25))	
	Declare @TblEmployeeIDS As Table(EmpAutoId INT)
	--CREATE TABLE #EmployeeIds(EmpAutoId INT)
	Declare @LevelRights As Table(LevelId INT)
	
	Create Table #TempHeader(id INT,Day1 INT,Day2 INT,Day3 INT,Day4 INT,Day5 INT,Day6 INT,Day7 INT,Day8 INT,Day9 INT,Day10 INT,Day11 INT,Day12 INT,Day13 INT,Day14 INT,Day15 INT,Day16 INT,Day17 INT,Day18 INT,Day19 INT,Day20 INT,Day21 INT,Day22 INT,Day23 INT,Day24 INT,Day25 INT,Day26 INT,Day27 INT,Day28 INT,Day29 INT,Day30 INT,Day31 INT)
	Declare @EmpLevel As Table(EmployeeId INT,LevelId INT,EffectiveDate DATETIME,Employee_Code_Id NVARCHAR(50),SwipeCard NVARCHAR(50))
	Declare @EmpDesignation As Table(EmployeeId INT,DesignationId INT,EffectiveDate DATETIME,Employee_Code_Id NVARCHAR(50),SwipeCard NVARCHAR(50))
	Declare @EmpType As Table(EmployeeId INT,TypeId INT,EffectiveDate DATETIME,Employee_Code_Id NVARCHAR(50),SwipeCard NVARCHAR(50))	
	Declare @EmpCategory As Table(EmployeeId INT,CategoryId INT,EffectiveDate DATETIME,Employee_Code_Id NVARCHAR(50),SwipeCard NVARCHAR(50))
	Declare @TempProcessedData As Table
	(
		EmpAutoId bigint,Date DATETIME,Employee_Code_Id NVARCHAR(20),InTime NVARCHAR(25),OutTime NVARCHAR(25),Attendance NVARCHAR(20),fnattendanceCode NVARCHAR(20),
		anattendanceCode NVARCHAR(20),Shift_Id INT,LevelId bigint,OtMins float,TotalDays NUMERIC(18,1),earlyoutmins float,LateInmins float,HrsWork NVARCHAR(10),ShiftId INT,Shift_FirstHalf_StartTime NVARCHAR(20),Shift_FirstHalf_EndTime NVARCHAR(20),Shift_SecondHalf_StartTime NVARCHAR(20),Shift_SecondHalf_EndTime NVARCHAR(20),NightOrMorn INT,InNotation char(1),OutNotation char(1)
	)	
	Create Table #EmpInOutDetails
	(
		Employee_code_id NVARCHAR(15),LevelId INT,date DATETIME,Otmins NUMERIC(18,1),TotalDays NUMERIC(18, 1),
		AttendanceFN NVARCHAR(25),AttendanceAN NVARCHAR(25),employee_code_id1 NVARCHAR(15),Designation NVARCHAR(250),empname NVARCHAR(150),
		Father_Name NVARCHAR(50),DesignationId INT,Grade_Level INT,OrderBy INT,Shift_FirstHalf_StartTime NVARCHAR(20),Shift_FirstHalf_EndTime NVARCHAR(20),Shift_SecondHalf_StartTime NVARCHAR(20),Shift_SecondHalf_EndTime NVARCHAR(20)
		,NightOrMorn INT,InNotation char(1),OutNotation char(1)	,
		PD DECIMAL(18,1) DEFAULT(0),AD DECIMAL(18,1) DEFAULT(0),WOD DECIMAL(18,1) DEFAULT(0),HD DECIMAL(18,1)DEFAULT(0),FH Decimal(18,1) DEFAULT(0),LD DECIMAL(18,1) DEFAULT(0),LOPD DECIMAL(18,1) DEFAULT(0),TOTD INT DEFAULT(0),CF1 nvarchar(30),CF2 nvarchar(30),CF3 nvarchar(30),CF4 nvarchar(30),CF5 nvarchar(30)
	)
	Create Table #AllEmpInOutDetails
	(
		Name NVARCHAR(50),FatherName NVARCHAR(50),Designation NVARCHAR(250),Employee_Code_Id NVARCHAR(15),Present NUMERIC(18,1),
		cusField1 nvarchar(20),cusField2 nvarchar(20),cusField3 nvarchar(20),cusField4 nvarchar(20),cusField5 nvarchar(20),
		Absent NUMERIC(18,1),Leave NVARCHAR(10) DEFAULT(0),Holiday NUMERIC(18,1),TotalOT NUMERIC(18,1),DesignationID INT,Grade_Level INT,OrderBy INT,CLAvailed decimal(18,2) DEFAULT(0),
		F_Day1 NVARCHAR(100) DEFAULT '-',F_Day2 NVARCHAR(100) DEFAULT '-',F_Day3 NVARCHAR(100) DEFAULT '-',F_Day4 NVARCHAR(100) DEFAULT '-',F_Day5 NVARCHAR(100) DEFAULT '-',F_Day6 NVARCHAR(100) DEFAULT '-',F_Day7 NVARCHAR(100) DEFAULT '-',F_Day8 NVARCHAR(100) DEFAULT '-',F_Day9 NVARCHAR(100) DEFAULT '-',F_Day10 NVARCHAR(100) DEFAULT '-',F_Day11 NVARCHAR(100) DEFAULT '-',F_Day12 NVARCHAR(100) DEFAULT '-',F_Day13 NVARCHAR(100) DEFAULT '-',F_Day14 NVARCHAR(100) DEFAULT '-',F_Day15 NVARCHAR(100) DEFAULT '-',F_Day16 NVARCHAR(100) DEFAULT '-',F_Day17 NVARCHAR(100) DEFAULT '-',F_Day18 NVARCHAR(100) DEFAULT '-',F_Day19 NVARCHAR(100) DEFAULT '-',F_Day20 NVARCHAR(100) DEFAULT '-',F_Day21 NVARCHAR(100) DEFAULT '-',F_Day22 NVARCHAR(100) DEFAULT '-',F_Day23 NVARCHAR(100) DEFAULT '-',F_Day24 NVARCHAR(100) DEFAULT '-',F_Day25 NVARCHAR(100) DEFAULT '-',F_Day26 NVARCHAR(100) DEFAULT '-',F_Day27 NVARCHAR(100) DEFAULT '-',F_Day28 NVARCHAR(100) DEFAULT '-',F_Day29 NVARCHAR(100) DEFAULT '-',F_Day30 NVARCHAR(100) DEFAULT '-',F_Day31 NVARCHAR(100) DEFAULT '-',
	    A_Day1 NVARCHAR(100) DEFAULT '-',A_Day2 NVARCHAR(100) DEFAULT '-',A_Day3 NVARCHAR(100) DEFAULT '-',A_Day4 NVARCHAR(100) DEFAULT '-',A_Day5 NVARCHAR(100) DEFAULT '-',A_Day6 NVARCHAR(100) DEFAULT '-',A_Day7 NVARCHAR(100) DEFAULT '-',A_Day8 NVARCHAR(100) DEFAULT '-',A_Day9 NVARCHAR(100) DEFAULT '-',A_Day10 NVARCHAR(100) DEFAULT '-',A_Day11 NVARCHAR(100) DEFAULT '-',A_Day12 NVARCHAR(100) DEFAULT '-',A_Day13 NVARCHAR(100) DEFAULT '-',A_Day14 NVARCHAR(100) DEFAULT '-',A_Day15 NVARCHAR(100) DEFAULT '-',A_Day16 NVARCHAR(100) DEFAULT '-',A_Day17 NVARCHAR(100) DEFAULT '-',A_Day18 NVARCHAR(100) DEFAULT '-',A_Day19 NVARCHAR(100) DEFAULT '-',A_Day20 NVARCHAR(100) DEFAULT '-',A_Day21 NVARCHAR(100) DEFAULT '-',A_Day22 NVARCHAR(100) DEFAULT '-',A_Day23 NVARCHAR(100) DEFAULT '-',A_Day24 NVARCHAR(100) DEFAULT '-',A_Day25 NVARCHAR(100) DEFAULT '-',A_Day26 NVARCHAR(100) DEFAULT '-',A_Day27 NVARCHAR(100) DEFAULT '-',A_Day28 NVARCHAR(100) DEFAULT '-',A_Day29 NVARCHAR(100) DEFAULT '-',A_Day30 NVARCHAR(100) DEFAULT '-',A_Day31 NVARCHAR(100) DEFAULT '-',	
	    PD DECIMAL(18,1) DEFAULT(0),AD DECIMAL(18,1) DEFAULT(0),WOD DECIMAL(18,1) DEFAULT(0),HD DECIMAL(18,1) DEFAULT(0),LD DECIMAL(18,1) DEFAULT(0),LOPD DECIMAL(18,1) DEFAULT(0),TOTD INT DEFAULT(0),FH Decimal(18,1)
	)
	--Assign Variable into some values
	SELECT @loca_EmployeeId=@EmployeeId,@loca_EmployeeName=@EmployeeName,@loca_LevelID=@LevelID,@loca_employeeCategory=@employeeCategory
	,@loca_employeeType=@employeeType,@loca_UserGroupName=@UserGroupName,@loca_GradeLevel=@GradeLevel,@loca_EmpSwipeCard=@EmpSwipeCard,
	@loca_ParentId=@ParentId,@loca_Fromdate1=@Fromdate1,@loca_Todate1=@Todate1,@loca_DesignationID=@DesignationID,@loca_Gender=@Gender,
	@loca_EmpAutoID=@EmpAutoID,@Fromdate2 = @loca_Fromdate1,@Diff = datediff(d,@loca_Fromdate1,@loca_Todate1) +1,@Count=1
	
	SELECT @isReportingto=ReportsBasedOn FROM TA_Tbl_Usergroup WHERE groupName=@loca_UserGroupName
    IF(@loca_EmployeeId IS  NOT NULL AND @loca_EmployeeId!='')
    BEGIN
		INSERT INTO @TblEmployeeID	  SELECT * FROM SplitupEmployeeCode(@loca_EmployeeId)	
	END
	IF(@loca_EmployeeName IS NOT NULL AND @loca_EmployeeName!='')
	BEGIN
		INSERT INTO @TblEmployeeName SELECT [Value] FROM dbo.[SplitUpEmployeeName](@loca_EmployeeName,',')
	END
    INSERT INTO	@TblEmployeeIDs  SELECT reportingEmpID FROM [dbo].[fn_TA_GetMappedEmployees_ReportingTo](@loca_EmpAutoID) as EmpAutoId  
    INSERT INTO @TblEmployeeIDs  SELECT reportingEmpID FROM [dbo].[fn_TA_GetMappedEmployees_SancAuthority](@loca_EmpAutoID) as EmpAutoId
    --For updating headers, inserted dummy value
	INSERT INTO #TempHeader(id) VALUES(1)
	INSERT INTO @LevelRights  SELECT ParentChildId FROM [dbo].[fn_TA_GetParentChild](@loca_UserGroupName,@loca_ParentId) as LevelId
	INSERT INTO @EmpLevel     SELECT Elevel.EmployeeId,EL.levelId,Elevel.EffectiveDate,Employee_Code_Id,SwipeCardNo  FROM 
	(
		SELECT MAX(el.EffectiveDate) as EffectiveDate,el.EmployeeId as EmployeeId,emp.Employee_Code_Id,emp.SwipeCardNo FROM TA_Tbl_EmployeeLevel el
		INNER JOIN ta_tbl_employee emp on el.EmployeeId = emp.Employee_Id 
		INNER JOIN @LevelRights LR on LR.LevelId=el.LevelId
		LEFT OUTER JOIN @TblEmployeeID EID on EID.EmployeeID=emp.Employee_Code_Id
		LEFT OUTER JOIN @TblEmployeeName EName on EName.EmployeeID=emp.Employee_Code_Id
		WHERE SwipeCardNo IS NOT NULL AND emp.Isdeleted=0
		AND (CAST (@loca_Gender as NVARCHAR(8)) = 'Both' or emp.Gender = CAST (@loca_Gender as NVARCHAR(8)))
		group by el.EmployeeId,Employee_Code_Id,emp.SwipeCardNo
	) ELevel
	INNER JOIN TA_Tbl_EmployeeLevel EL on EL.EffectiveDate=ELevel.EffectiveDate AND EL.employeeid=ELevel.EmployeeId
	INNER JOIN @LevelRights LR on LR.LevelId=EL.LevelId
	AND EL.EffectiveDate <= CONVERT(DATETIME,CAST (@loca_Todate1 as NVARCHAR(50)), 102) AND EL.LevelId IS NOT NULL 
	--Employee Designation INSERT in Temporary table
	INSERT INTO @EmpDesignation EXEC Proc_TA_EmpDesignation @loca_Todate1,@loca_EmployeeId,@loca_DesignationID 	
	INSERT INTO @EmpType EXEC[Proc_TA_EmpType] @loca_Todate1,@loca_EmployeeId,@loca_employeeType 
	INSERT INTO @EmpCategory EXEC[Proc_TA_EmpCategory] @loca_Todate1,@loca_EmployeeId,@loca_employeeCategory
	SELECT SwipeCardNo,inActiveDate into #InActiveEmployees FROM ta_tbl_employee WHERE inactivedate IS NOT NULL AND Employee_Status=0
    
	INSERT INTO @TempProcessedData
	SELECT EL.EmployeeId,pd.Date,pd.Employee_Code_Id,pd.InTime,pd.OutTime,pd.Attendance,pd.fnattendanceCode,pd.anattendanceCode,
	pd.Shift_Id,'',pd.OtMins,pd.TotalDays,pd.earlyoutmins,pd.LateInmins,pd.HrsWork,pd.Shift_Id,S.Shift_FirstHalf_StartTime,
	S.Shift_FirstHalf_EndTime,S.Shift_SecondHalf_StartTime,S.Shift_SecondHalf_EndTime,0,InNotation,OutNotation 
	FROM TA_Tbl_ProcessedData PD
	INNER JOIN @EmpLevel EL on EL.SwipeCard=PD.Employee_Code_Id 
	INNER JOIN @EmpCategory EC on EC.EmployeeId=EL.EmployeeId
	INNER JOIN @EmpType ET on ET.EmployeeId=EL.EmployeeId
	INNER JOIN @EmpDesignation ED on ED.EmployeeId=EL.EmployeeId
	INNER JOIN #InActiveEmployees IA on (EL.SwipeCard=IA.SwipeCardNo AND pd.Date<=IA.inactivedate)
	Left Outer JOIN TA_Tbl_Shift S on S.Shift_Id=PD.Shift_Id  WHERE (PD.Date >=@loca_Fromdate1 AND PD.Date <= @loca_Todate1)

	INSERT INTO @TempProcessedData
	SELECT EL.EmployeeId,pd.Date,pd.Employee_Code_Id,pd.InTime,pd.OutTime,pd.Attendance,pd.fnattendanceCode,pd.anattendanceCode,
	pd.Shift_Id,'',pd.OtMins,pd.TotalDays,pd.earlyoutmins,pd.LateInmins,pd.HrsWork,pd.Shift_Id,S.Shift_FirstHalf_StartTime,
	S.Shift_FirstHalf_EndTime,S.Shift_SecondHalf_StartTime,S.Shift_SecondHalf_EndTime,0,InNotation,OutNotation
	FROM TA_Tbl_ProcessedData PD
	INNER JOIN @EmpLevel EL on EL.SwipeCard=PD.Employee_Code_Id
	INNER JOIN @EmpCategory EC on EC.EmployeeId=EL.EmployeeId
	INNER JOIN @EmpType ET on ET.EmployeeId=EL.EmployeeId
	INNER JOIN @EmpDesignation ED on ED.EmployeeId=EL.EmployeeId
	Left Outer JOIN TA_Tbl_Shift S on S.Shift_Id=PD.Shift_Id
	WHERE (PD.Date >=@loca_Fromdate1 AND PD.Date <= @loca_Todate1)
	AND EL.SwipeCard not in(SELECT DISTINCT swipecardno FROM #InActiveEmployees)
	
	UPDATE @TempProcessedData SET NightOrMorn= CASE WHEN SUBSTRING(S.Shift_StartTime,7,8)!='PM' AND SUBSTRING(S.Shift_Endtime,7,8)!='AM' then 0 
													ELSE 1 END 
	FROM @TempProcessedData PD INNER JOIN TA_Tbl_Shift S on PD.Shift_Id=S.Shift_Id
	
	INSERT INTO #EmpInOutDetails 
	SELECT	E.Employee_code_id,
			'',
			TPD.date, 
			TPD.OtMins, 
			TPD.TotalDays,
			AttendanceFN=	
			CASE	WHEN TPD.FNAttendanceCode NOT IN('Absent','Present','Weekly Off','Comp Off','Holiday','RH','CL','EL') then '' + lower(FNAttendanceCode) + ''
					WHEN TPD.FNAttendanceCode='Absent'		THEN 'A'
					WHEN TPD.FNAttendanceCode='Present'		THEN CASE	WHEN (TPD.LateInmins <> '0' )					THEN '*P'  ELSE 'P'	  END
					WHEN TPD.FNAttendanceCode='Weekly Off'	THEN CASE	WHEN (InTime IS NOT NULL  AND Intime<> '00:00')	THEN '**o' ELSE 'Off' END
					WHEN TPD.FNAttendanceCode='Holiday'		THEN CASE	WHEN (Intime<> '00:00' or OutTime<>'00:00')		THEN '**H' ELSE 'H'	  END
					WHEN TPD.FNAttendanceCode='RH'			THEN CASE	WHEN (Intime<> '00:00' or OutTime<>'00:00')		THEN '**R' ELSE 'RH'  END
					WHEN TPD.FNAttendanceCode='Comp Off'	THEN 'CO'
					WHEN TPD.FNAttendanceCode='CL'			THEN 'CL'
					WHEN TPD.FNAttendanceCode='EL'			THEN 'EL'
			END,
			AttendanceAN = 
			CASE	WHEN TPD.ANAttendanceCode NOT IN('Absent','Present','Weekly Off','Comp Off','Holiday','RH','CL','EL') then '' + lower(ANAttendanceCode) + ''
					WHEN TPD.ANAttendanceCode='Absent'		THEN 'A'
					WHEN TPD.ANAttendanceCode='Present'		THEN CASE	WHEN (TPD.earlyoutmins <> '0' )				  THEN '*P'		ELSE 'P'	END
					WHEN TPD.ANAttendanceCode='Weekly Off'	THEN CASE	WHEN (Outtime IS NOT NULL  AND Outtime<> '00:00')THEN '**o'	ELSE 'Off'	END
					WHEN TPD.ANAttendanceCode='Holiday'		THEN CASE	WHEN (Intime<> '00:00' or OutTime<>'00:00')   THEN '**H'	ELSE 'H'	END
					WHEN TPD.ANAttendanceCode='RH'			THEN CASE	WHEN (Intime<> '00:00' or OutTime<>'00:00')   THEN '**R'	ELSE 'RH'	END
					WHEN TPD.ANAttendanceCode='Comp Off'	THEN 'CO'
					WHEN TPD.ANAttendanceCode='CL'			THEN 'CL'
					WHEN TPD.ANAttendanceCode='EL'			THEN 'EL'
			END,
			TPD.employee_code_id, 
			REPLACE(T.TitleName,'&amp;','&') as Designation,
			E.Employee_FirstName + SPACE(1) + ISNULL(E.Employee_MiddleName,'') + SPACE(1) + ISNULL(E.employee_lastname,''), 
			E.Father_Name,
			T.TitleId,
			G.Grade_Level,
			0,
			TPD.Shift_FirstHalf_StartTime,
			TPD.Shift_FirstHalf_EndTime,
			TPD.Shift_SecondHalf_StartTime,
			TPD.Shift_SecondHalf_EndTime,
			TPD.NightOrMorn,
			InNotation,
			OutNotation,
			CASE WHEN TPD.fnattendanceCode='Present'	THEN 0.5 ELSE 0 END + CASE WHEN TPD.anattendanceCode='Present'	THEN 0.5 ELSE 0 END,
			CASE WHEN TPD.fnattendanceCode='Absent'		THEN 0.5 ELSE 0 END + CASE WHEN TPD.anattendanceCode='Absent'	THEN 0.5 ELSE 0 END,
			CASE WHEN TPD.fnattendanceCode='Weekly Off' THEN 0.5 ELSE 0 END + CASE WHEN TPD.anattendanceCode='Weekly Off' THEN 0.5 ELSE 0 END,			
			CASE WHEN TPD.fnattendanceCode='Holiday'	THEN 0.5 Else 0 END + CASE WHEN TPD.anattendanceCode='Holiday' THEN 0.5 ELSE 0 END,
			CASE WHEN TPD.fnattendanceCode='RH'	THEN 0.5 Else 0 END + CASE WHEN TPD.anattendanceCode='RH' THEN 0.5 ELSE 0 END,
			CASE WHEN TPD.fnattendanceCode NOT IN('Absent','Present','Weekly Off','Holiday','LOP') THEN 0.5 ELSE 0 END + CASE WHEN TPD.anattendanceCode NOT IN('Absent','Present','Weekly Off','Holiday','LOP') THEN 0.5 ELSE 0 END,
			CASE WHEN TPD.fnattendanceCode='LOP'		THEN 0.5 ELSE 0 END + CASE WHEN TPD.anattendanceCode='LOP' THEN 0.5 ELSE 0 END,
			0,E.CustomField1,E.CustomField2,E.CustomField3,E.CustomField4,E.CustomField5
	FROM  @TempProcessedData TPD  
	INNER JOIN TA_Tbl_Employee E ON E.SwipeCardNo=TPD.Employee_Code_Id  
	INNER JOIN @EmpDesignation EDesig ON EDesig.EmployeeID= E.Employee_Id  
	INNER JOIN TA_Tbl_Title T ON  T.TitleId=EDesig.DesignationId  
	INNER JOIN TA_Tbl_Grade G ON T.GradeId = G.Grade_ID 
	WHERE (E.Isdeleted=0 AND E.report_exclude=0)
	AND (ISNULL(@isReportingto,0) = 0 or E.Employee_ID IN (SELECT * FROM @TblEmployeeIDS))
	
	---Updating OD Notation 
    UPDATE #EmpInOutDetails SET
		AttendanceFN = [dbo].Func_TA_MusterRoll_OD_Notation(PD.[Date], PD.FNAttendanceCode, PD.Employee_Code_Id,  'FN', PD.Shift_Id, AttendanceFN),
		AttendanceAN = [dbo].Func_TA_MusterRoll_OD_Notation(PD.[Date], PD.ANAttendanceCode, PD.Employee_Code_Id, 'AN', PD.Shift_Id, AttendanceAN)
	FROM #EmpInOutDetails IOD
	INNER JOIN TA_Tbl_ManualTimeEntry MD ON (IOD.Employee_code_id=MD.ManualTimeEntry_Employee_Id)
	INNER JOIN TA_Tbl_ProcessedData PD ON IOD.Employee_code_id = PD.Employee_Code_Id AND PD.[Date] = IOD.[date]
	WHERE PD.ProcessFlag = 1
	AND (MD.ManualTimeEntry_InDate>=@loca_Fromdate1 AND MD.ManualTimeEntry_OutDate<=@loca_Todate1)
	AND MD.IsOnDuty=1 AND MD.Isdeleted=0
    
    UPDATE #EmpInOutDetails SET AttendanceFN= CASE	WHEN I.AttendanceFN = 'A' THEN I.AttendanceFN
													WHEN (I.AttendanceFN ='P' OR I.AttendanceFN ='*P' OR I.AttendanceFN ='H' OR I.AttendanceFN ='**H' OR I.AttendanceFN ='Off' OR I.AttendanceFN ='**O') AND InNotation='M' then AttendanceFN+'#' 
													ELSE AttendanceFN 
											  END,
	AttendanceAN= CASE	WHEN  I.AttendanceAN = 'A' THEN I.AttendanceAN
						WHEN (I.AttendanceAN='P' OR I.AttendanceAN='*P' OR I.AttendanceAN='H' OR I.AttendanceAN='**H' OR I.AttendanceAN='Off' OR I.AttendanceAN='**O') AND OutNotation='M' then AttendanceAN+'#' else AttendanceAN END
	FROM #EmpInOutDetails I WHERE (InNotation='M' or OutNotation='M')
	
	

	INSERT INTO #AllEmpInOutDetails (Employee_Code_Id,Name,Designation,FatherName,DesignationID,Grade_Level,OrderBy,cusField1,cusField2,cusField3,cusField4,cusField5)
	SELECT DISTINCT Employee_code_id,empname,Designation,Father_Name,DesignationID,Grade_Level,OrderBy,CF1,CF2,CF3,CF4,CF5 FROM #EmpInOutDetails 
	
	DECLARE @LeaveTypeID INT
	SELECT @LeaveTypeID =LeaveType_Id FROM TA_tbl_LeaveType WHERE LeaveType='CL'
	
	--Inserts the Leave Count of employee in the temporary table
	if(@LeaveTypeID<>'')
	begin
		Declare @LeaveCount As Table(Employee_code_Id NVARCHAR(15),LeaveCount NUMERIC(18,1),LeaveCountFraction NVARCHAR(10))
		
		INSERT INTO @LeaveCount 
		SELECT e.employee_code_id,ISNULL(sum(convert(decimal(18,1),NumberOfDays)),0) as LeaveCount,'' as LeaveCountFraction 
		FROM ta_tbl_leaveapplication la INNER JOIN ta_tbl_employee e on e.Employee_Id = la.Employee_Id
		WHERE ((la.Leave_FromDate between @loca_Fromdate1 AND @loca_Todate1)or (la.Leave_ToDate between @loca_Fromdate1 AND @loca_Todate1))
		AND la.Status = 'Approved' AND la.LeaveType_Id=@LeaveTypeID AND la.IsDeleted=0 	
		group by e.employee_code_id
		
		UPDATE @LeaveCount SET LeaveCountFraction=ISNULL(LeaveCount,0)- Floor(ISNULL(LeaveCount,0))	
		UPDATE @LeaveCount SET LeaveCountFraction=ltrim(CAST ((case CAST (Floor(LeaveCount) as NVARCHAR(10)) WHEN '0' then '' else CAST (Floor(LeaveCount) as NVARCHAR(10)) END) as NVARCHAR(10)) + ' ' +
				case LeaveCountFraction WHEN '0.5' then '1/2' else ''END )	
		UPDATE #AllEmpInOutDetails SET Leave =ISNULL(LC.LeaveCountFraction,0) FROM @LeaveCount LC
		INNER JOIN #AllEmpInOutDetails AD on LC.Employee_code_Id = AD.Employee_Code_Id
	END
	--select @loca_Fromdate1,@loca_Todate1
	--Calculating Total Days of each attendance
	UPDATE #AllEmpInOutDetails set PD=a.PD,AD=a.AD,WOD=a.WOD,HD=a.HD,FH=a.FH,LD=a.LD,LOPD=a.LOPD,TOTD=a.TOTD,TotalOT=a.TotalOT FROM #AllEmpInOutDetails AD INNER JOIN
	(
		select employee_code_id1, PD=SUM(isnull(ED.PD,0)),AD=SUM(isnull(ED.AD,0)),WOD=SUM(isnull(ED.WOD,0)),HD=SUM(isnull(ED.HD,0)),
		FH=Sum(isnull(ED.FH,0)),LD=SUM(isnull(ED.LD,0)),LOPD=SUM(isnull(ED.LOPD,0)),
		TOTD=DATEDIFF(d,@loca_Fromdate1,DATEADD(d,1,@loca_Todate1)),
		TotalOT=SUM(Otmins)
		from #EmpInOutDetails ED group by employee_code_id1
	)a on a.employee_code_id1=AD.Employee_Code_Id
	

	SET @Count=1
	DECLARE @SQLUS NVARCHAR(max)
	DECLARE @SQLHeader NVARCHAR(max)
	WHILE(@Count <=31)
	BEGIN
		--Creating Table Header
		SET @SQLHeader=''
		SET @SQLUS=''
		SET @SQLHeader = @SQLHeader + ' UPDATE #TempHeader SET Day'+CAST(@Count as NVARCHAR(10)) + '=' + CAST (datepart(d,@loca_Fromdate1)as NVARCHAR(10)) + 'WHERE id=1'	
		EXEC(@SQLHeader)
		--Updating data of particular date.
		IF(@Count <=@Diff)
		BEGIN			
			SET @SQLUS = @SQLUS + ' UPDATE AD SET F_Day'+ CAST (@Count as NVARCHAR(10)) + ' = ED.AttendanceFN,A_Day'+ CAST (@Count as NVARCHAR(10)) + ' = ED.AttendanceAN'
			SET @SQLUS = @SQLUS + ' FROM  #EmpInOutDetails ED INNER JOIN #AllEmpInOutDetails AD on AD.Employee_code_id=	ED.Employee_code_id AND ED.date =   CONVERT(DATETIME, ''' + @loca_Fromdate1 + ''' , 102)'
			EXEC(@SQLUS)
		END		
		SET @loca_Fromdate1=dateadd(d,1,@loca_Fromdate1)
		SET @Count=@Count +1
	END
    --for CLAvailed 
	UPDATE #AllEmpInOutDetails SET  CLAvailed= ISNULL(A.LeaveAvailed,'0.0')
	FROM #AllEmpInOutDetails TDA INNER JOIN 
	(
			SELECT (sum(CASE fnattendanceCode WHEN 'CL' THEN 0.5 Else 0 END) + sum(CASE anattendanceCode WHEN 'CL' THEN 0.5 Else 0 END)) as LeaveAvailed,
					PD.Employee_Code_Id 
			FROM TA_tbl_ProcessedData pd INNER JOIN TA_tbl_YearSetting on TA_tbl_YearSetting.ISActive=1
			Inner JOIN #AllEmpInOutDetails TA on TA.Employee_Code_Id=pd.Employee_Code_Id
			WHERE (Date between TA_tbl_YearSetting.FromDate AND @loca_Todate1) AND (FNAttendanceCode='CL' or ANAttendanceCode='CL')
			group by pd.Employee_Code_Id
	) as A on TDA.Employee_Code_Id = A.Employee_code_Id


	--- if the value is less than 0, update the value as 0
	UPDATE #AllEmpInOutDetails SET CLAvailed=0.0 WHERE ISNULL(CLAvailed,0)<0
	UPDATE #AllEmpInOutDetails SET PD=0.0 WHERE ISNULL(PD,0)<0
	UPDATE #AllEmpInOutDetails SET AD=0.0 WHERE ISNULL(AD,0)<0
	UPDATE #AllEmpInOutDetails SET WOD=0.0 WHERE ISNULL(WOD,0)<0
	UPDATE #AllEmpInOutDetails SET HD=0.0 WHERE ISNULL(HD,0)<0
	UPDATE #AllEmpInOutDetails SET FH=0.0 WHERE ISNULL(FH,0)<0
	UPDATE #AllEmpInOutDetails SET LD=0.0 WHERE ISNULL(LD,0)<0
	UPDATE #AllEmpInOutDetails SET LOPD=0.0 WHERE ISNULL(LOPD,0)<0	
	SELECT	Employee_Code_Id,
			Name,
			Designation,			
			CLAvailed,cusField1,cusField2,cusField3,cusField4,cusField5,
			F_Day1,F_Day2,F_Day3,F_Day4,F_Day5,F_Day6,F_Day7,F_Day8,F_Day9,F_Day10,F_Day11,F_Day12,F_Day13,F_Day14,F_Day15,
			F_Day16,F_Day17,F_Day18,F_Day19,F_Day20,F_Day21,F_Day22,F_Day23,F_Day24,F_Day25,F_Day26,F_Day27,F_Day28,F_Day29,F_Day30,F_Day31,
			FatherName,
			Present,
			Absent,
			Leave,
			Holiday,
			TotalOT,			
			DesignationID,
			Grade_Level,
			OrderBy,
			PD,
			AD,
			WOD,
			HD,
			FH,
			LD,
			LOPD,
			TotalOTHrs=dbo.ConvertMinsToHours(TotalOT),
			TOTD,
			ToTDHrs=dbo.GetTotdalworkingDaysToWorkingHours(TOTD),
			A_Day1,A_Day2,A_Day3,A_Day4,A_Day5,A_Day6,A_Day7,A_Day8,A_Day9,A_Day10,A_Day11,A_Day12,A_Day13,A_Day14,A_Day15,
			A_Day16,A_Day17,A_Day18,A_Day19,A_Day20,A_Day21,A_Day22,A_Day23,A_Day24,A_Day25,A_Day26,A_Day27,A_Day28,A_Day29,A_Day30,A_Day31,			
			AFDay1=REPLACE(F_Day1,'#','')+' / '+REPLACE(A_Day1,'#',''),
			AFDay2=REPLACE(F_Day2,'#','')+' / '+REPLACE(A_Day2,'#',''),
			AFDay3=REPLACE(F_Day3,'#','')+' / '+REPLACE(A_Day3,'#',''),
			AFDay4=REPLACE(F_Day4,'#','')+' / '+REPLACE(A_Day4,'#',''),
			AFDay5=REPLACE(F_Day5,'#','')+' / '+REPLACE(A_Day5,'#',''),
			AFDay6=REPLACE(F_Day6,'#','')+' / '+REPLACE(A_Day6,'#',''),
			AFDay7=REPLACE(F_Day7,'#','')+' / '+REPLACE(A_Day7,'#',''),
			AFDay8=REPLACE(F_Day8,'#','')+' / '+REPLACE(A_Day8,'#',''),
			AFDay9=REPLACE(F_Day9,'#','')+' / '+REPLACE(A_Day9,'#',''),
			AFDay10=REPLACE(F_Day10,'#','')+' / '+REPLACE(A_Day10,'#',''),
			AFDay11=REPLACE(F_Day11,'#','')+' / '+REPLACE(A_Day11,'#',''),
			AFDay12=REPLACE(F_Day12,'#','')+' / '+REPLACE(A_Day12,'#',''),
			AFDay13=REPLACE(F_Day13,'#','')+' / '+REPLACE(A_Day13,'#',''),
			AFDay14=REPLACE(F_Day14,'#','')+' / '+REPLACE(A_Day14,'#',''),
			AFDay15=REPLACE(F_Day15,'#','')+' / '+REPLACE(A_Day15,'#',''),
			AFDay16=REPLACE(F_Day16,'#','')+' / '+REPLACE(A_Day16,'#',''),
			AFDay17=REPLACE(F_Day17,'#','')+' / '+REPLACE(A_Day17,'#',''),
			AFDay18=REPLACE(F_Day18,'#','')+' / '+REPLACE(A_Day18,'#',''),
			AFDay19=REPLACE(F_Day19,'#','')+' / '+REPLACE(A_Day19,'#',''),
			AFDay20=REPLACE(F_Day20,'#','')+' / '+REPLACE(A_Day20,'#',''),
			AFDay21=REPLACE(F_Day21,'#','')+' / '+REPLACE(A_Day21,'#',''),
			AFDay22=REPLACE(F_Day22,'#','')+' / '+REPLACE(A_Day22,'#',''),
			AFDay23=REPLACE(F_Day23,'#','')+' / '+REPLACE(A_Day23,'#',''),
			AFDay24=REPLACE(F_Day24,'#','')+' / '+REPLACE(A_Day24,'#',''),
			AFDay25=REPLACE(F_Day25,'#','')+' / '+REPLACE(A_Day25,'#',''),
			AFDay26=REPLACE(F_Day26,'#','')+' / '+REPLACE(A_Day26,'#',''),
			AFDay27=REPLACE(F_Day27,'#','')+' / '+REPLACE(A_Day27,'#',''),
			AFDay28=REPLACE(F_Day28,'#','')+' / '+REPLACE(A_Day28,'#',''),
			AFDay29=REPLACE(F_Day29,'#','')+' / '+REPLACE(A_Day29,'#',''),
			AFDay30=REPLACE(F_Day30,'#','')+' / '+REPLACE(A_Day30,'#',''),
			AFDay31=REPLACE(F_Day31,'#','')+' / '+REPLACE(A_Day31,'#','')			
	FROM #AllEmpInOutDetails A ORDER BY A.Grade_Level,CAST(ISNULL(A.Orderby,100000)as INT),Name
	
	SELECT * FROM #TempHeader
	
	
	--Drop Table #EmployeeID
	--Drop Table #EmployeeIds
	--Drop Table #LevelRights
	--DROP TABLE #employeename
	Drop TAble #InActiveEmployees
	--DROP TABLE #empcategory
	--DROP TABLE #emptype
	--DROP TABLE #EmpLevel
	--DROP TABLE #EmpDesignation
	--DROP TABLE #EmpInOutDetails
	--DROP TABLE #AllEmpInOutDetails
	--DROP TABLE #TempProcessedData
END